package com.tangdi.mkfile.gzffmx;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.tangdi.ReportFileInfo;
import com.tangdi.Utils;
import com.tangdi.Bean.CZGZ_DWCOUNT;
import com.tangdi.Bean.CZGZ_GZLB;
import com.tangdi.Bean.CZGZ_SJGZ;
import com.tangdi.Bean.CZGZ_ZGGZMX;
import com.tangdi.Bean.XM_BM_XM_MC;
import com.tangdi.Dao.impl.GongziMXImpl;

public class CreateGZFFMXExcel_ByDW {
	private Logger logger = Logger.getLogger(CreateGZFFMXExcel_ByDW.class);
	String excel_url_root = Utils.getFilePath("root_path");
	private String folder_Name = ReportFileInfo.FOLDER_NAME_GZTIAO.getName();
	private String file_Type = "excel";
	private String file_name_type = ReportFileInfo.FILE_NAME_GZTIAO.getName();
	public int createExcel(Map<String,List<XM_BM_XM_MC>> map_bm_mc_list,List<CZGZ_DWCOUNT> listDWCOUNT,List<CZGZ_GZLB> list_gz_lb,String month) {
		StringBuffer sql_select = null;
		Map<String,String> map_lb_select = new HashMap<String,String>();
		HSSFWorkbook wb = null;
		Sheet sheet = null;

		int initSize = 0;//最长列
		int excel_rows = 0;//行数
		
		for(int i=0; i < map_bm_mc_list.size(); i++){//组装工资明细查询sql
			sql_select = new StringBuffer("SELECT TRIM(YF) AS YF, TRIM(NME) AS NME, ");
			List<XM_BM_XM_MC> list_BM_MC = map_bm_mc_list.get(list_gz_lb.get(i).getLB_BM());
			if(initSize < list_BM_MC.size()){//算出最长列
				initSize = list_BM_MC.size();
			}
			for(int j=0; j < list_BM_MC.size(); j++){
				sql_select.append("coalesce(TO_CHAR(to_number("+ list_BM_MC.get(j).getXM_BM() +"),'9999999999990.00'),'0.00') AS " +list_BM_MC.get(j).getXM_BM() + ",");
			}
			map_lb_select.put(list_gz_lb.get(i).getLB_BM(), sql_select.substring(0, sql_select.length() - 1));
		}
		
		initSize = initSize + 3;
	    try {
			GongziMXImpl gzmx = new GongziMXImpl();
	    	String excel_url = "";
	    	
			//listDWCOUNT.size()
			for(int i = 0; i < listDWCOUNT.size(); i++){
				logger.info("单位名称：" + listDWCOUNT.get(i).getDW_MC() + " | 单位类别：" + listDWCOUNT.get(i).getGZ_LB_DM());
				if(i == 0){
					wb = new HSSFWorkbook();
					sheet = wb.createSheet("sheet1");
					excel_rows = 0;
					excel_url = excel_url_root + "/" + month + "/" + file_Type + "/" + folder_Name + "/" + month + "_"+ file_name_type + "_" + listDWCOUNT.get(i).getDW_DM() +"_" + listDWCOUNT.get(i).getDW_MC() + ".xls";
					//在填写文件路径时，一定要写上具体的文件名称（xx.txt），否则会出现拒绝访问。
					File file = new File(excel_url);
					if(!file.exists()){
					    //先得到文件的上级目录，并创建上级目录，在创建文件
						file.getParentFile().mkdirs();
						file.createNewFile();
					}
				}
				//字体
				Font headerFont = getFont(wb, "微软雅黑", 18);
				Font contextFont = getFont(wb, "微软雅黑", 12);
				//样式 
				CellStyle headerStyle = getCellStyle(wb, "noboder", CellStyle.ALIGN_LEFT, headerFont);
				CellStyle contentStyle = getCellStyle(wb, "border", CellStyle.ALIGN_RIGHT, contextFont);
				CellStyle contentStyle_center = getCellStyle(wb, "border", CellStyle.ALIGN_CENTER, contextFont);
				CellStyle nameStyle = getCellStyle(wb, "border", CellStyle.ALIGN_CENTER, contextFont);
				
				
				String gz_lb = listDWCOUNT.get(i).getGZ_LB_DM();
				String gz_dw_dm = listDWCOUNT.get(i).getDW_DM();
				
				int sj_Size = Integer.valueOf(map_bm_mc_list.get(gz_lb).size()) + 3;
				
				for(int a = 0; a < initSize; a++){
					sheet.setColumnWidth(a, 12*256);
			    }
				sheet.setColumnWidth(0, 8*256);
				

				Row row00 = sheet.createRow(0 + excel_rows);
				addCell_Value(row00, 0, listDWCOUNT.get(i).getDW_MC(), headerStyle);
				//工资条 - list
				List<CZGZ_ZGGZMX> listmx=new ArrayList<CZGZ_ZGGZMX>();
				
				List<XM_BM_XM_MC> list_MX_BM_MC = map_bm_mc_list.get(listDWCOUNT.get(i).getGZ_LB_DM());
				listmx = gzmx.listGZMXReportByLB(month, gz_dw_dm, gz_lb,list_MX_BM_MC, map_lb_select.get(gz_lb));
				int listmx_Size = listmx.size();
				for(int j = 0; j < listmx.size(); j++){

					CZGZ_ZGGZMX gz_mx = listmx.get(j);

					//列名--start
					Row row01 = sheet.createRow(2*j + 1+ excel_rows);
					addCell_Value(row01, 0, "序号", nameStyle);
					addCell_Value(row01, 1, "月份", nameStyle);
					addCell_Value(row01, 2, "姓名", nameStyle);
					for(int h = 0; h < list_MX_BM_MC.size(); h++){//显示工资科目名称
						addCell_Value(row01, h + 3, list_MX_BM_MC.get(h).getXM_MC(), nameStyle);
						}
					if(sj_Size < initSize){
						for(int a = 0; a < (initSize - sj_Size); a++){
							addCell_Value(row01, a + sj_Size, "", nameStyle);
						}
					}
					//列名--end
					
	                //列值 --start
					Row row02 = sheet.createRow(2*j + 2 + excel_rows);
					addCell_Value(row02, 0, String.valueOf(j + 1), contentStyle_center);
					addCell_Value(row02, 1, gz_mx.getYF().substring(2, 4) + "-" +gz_mx.getYF().substring(4, 6), contentStyle_center);
					addCell_Value(row02, 2, gz_mx.getNME(), contentStyle_center);
					for(int k = 0; k < list_MX_BM_MC.size(); k++){//根据工资类别反射查找工资科目
						String gz_value = (String)Utils.getFieldReflect(gz_mx,CZGZ_ZGGZMX.class,list_MX_BM_MC.get(k).getXM_BM());
						addCell_Value(row02, k + 3, (gz_value.trim()).equals("0.00")  ? " " : (gz_value.trim()), contentStyle);
						}
					if(sj_Size < initSize){
						for(int b = 0; b < (initSize - sj_Size); b++){
							addCell_Value(row02, b + sj_Size, "", contentStyle);
						}
					}
					//列值 --end
					}
				sheet.addMergedRegion(new CellRangeAddress(excel_rows, excel_rows, 0, initSize - 1));
				
				if(i == (listDWCOUNT.size() - 1)){
					FileOutputStream os = new FileOutputStream(excel_url);
					wb.write(os);
					os.close();
				}else{
					if(!(listDWCOUNT.get(i).getDW_DM()).equals(listDWCOUNT.get(i+1).getDW_DM())){
						FileOutputStream os = new FileOutputStream(excel_url);
						wb.write(os);
						os.close();

						wb = new HSSFWorkbook();
						sheet = wb.createSheet("sheet1");
						excel_rows = 0;
						excel_url = excel_url_root + "/" + month + "/" + file_Type + "/" + folder_Name + "/" + month + "_"+ file_name_type + "_" + listDWCOUNT.get(i + 1).getDW_DM() +"_" + listDWCOUNT.get(i + 1).getDW_MC() + ".xls";
						//在填写文件路径时，一定要写上具体的文件名称（xx.txt），否则会出现拒绝访问。
						File file = new File(excel_url);
						if(!file.exists()){
						    //先得到文件的上级目录，并创建上级目录，在创建文件
							file.getParentFile().mkdirs();
							file.createNewFile();
						}
					}else{
							excel_rows = excel_rows + 2 * listmx_Size + 10;
						}
					}
				
				}
	    } catch (Exception e) {
	    	e.printStackTrace();
	    	return -1;
		}
	    return 0;
	}
	
	public static CellStyle getCellStyle(HSSFWorkbook wb, String borderType, short align, Font font){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(align);
		if(borderType.equals("border")){
			style.setBorderTop(CellStyle.BORDER_THIN);
			style.setBorderBottom(CellStyle.BORDER_THIN);
			style.setBorderLeft(CellStyle.BORDER_THIN);
			style.setBorderRight(CellStyle.BORDER_THIN);
		}
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
		style.setLocked(true);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}
	public static Font getFont(HSSFWorkbook wb, String fontName, int fontSize){
		Font font = wb.createFont();
		font.setFontName(fontName);
		font.setFontHeightInPoints((short)fontSize);
		font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		return font;
	}
	public static void addCell_Value(Row row, int a, String value, CellStyle cellStyle){
		Cell cell = row.createCell(a);
		cell.setCellValue(value);
		cell.setCellStyle(cellStyle);
	}
	
	public static void main(String[] args) {
		//年月
		String month = "201905";
		List<CZGZ_DWCOUNT> listDWCOUNT=new ArrayList<CZGZ_DWCOUNT>();
		GongziMXImpl gz_dw_count = new GongziMXImpl();
		listDWCOUNT = gz_dw_count.listGZDWCount(month);	
		
		//工资类别，科目项信息
		Map<String,List<XM_BM_XM_MC>> map_bm_mc_list = new HashMap<String,List<XM_BM_XM_MC>>();
		//工资类别list
		List<CZGZ_GZLB> listlb=new ArrayList<CZGZ_GZLB>();
		GongziMXImpl gzlb = new GongziMXImpl();
		listlb = gzlb.listGZLBReport(month);	
		//实际工资科目 按工资类别
		List<CZGZ_SJGZ> list_sjgz=new ArrayList<CZGZ_SJGZ>();
		GongziMXImpl sjgz = new GongziMXImpl();
		list_sjgz = sjgz.listSJGZReport(month);	
		
		//组装工资类别 ，实际工资科目项，对应好关系；方便后续使用
		for(int i=0;i<listlb.size();i++){
			List<XM_BM_XM_MC> bm_mc_list = new ArrayList<XM_BM_XM_MC>();
			for(int j=0; j<list_sjgz.size(); j++){
				XM_BM_XM_MC bm_mc= new XM_BM_XM_MC();
				if((listlb.get(i).getLB_BM().trim()).equals(list_sjgz.get(j).getGZ_LB().trim())){
					bm_mc.setXM_BM(Utils.getXM_BM(list_sjgz.get(j).getXM_BM().trim()));
					bm_mc.setXM_MC(list_sjgz.get(j).getXM_MC().trim());
	
					bm_mc_list.add(bm_mc);
				}
			}
			map_bm_mc_list.put(listlb.get(i).getLB_BM().trim(), bm_mc_list);
		}
		
		CreateGZFFMXExcel_ByDW c = new CreateGZFFMXExcel_ByDW();
	    c.createExcel(map_bm_mc_list,listDWCOUNT,listlb,month);
	    System.out.println("制作完成");
	}
}
